﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
//using Excel;
using System.Windows.Forms;

namespace Common
{
    public class InsertPicToExcel
    {
        private Excel.Application m_objExcel = null;
        private Excel.Workbooks m_objBooks = null;
        private Excel._Workbook m_objBook = null;
        private Excel.Sheets m_objSheets = null;
        private Excel._Worksheet m_objSheet = null;
        private Excel.Range m_objRange = null;
        private object m_objOpt = System.Reflection.Missing.Value;

        public void Open()
        {
            this.Open(String.Empty);            
        }

        public void Open(string TemplateFilePath)
        {
            m_objExcel = new Microsoft.Office.Interop.Excel.Application();
            m_objExcel.Visible = false;
            m_objExcel.DisplayAlerts = false;

            //if (m_objExcel.Version != "11.0")
            //{
            //    MessageBox.Show("This is not a 11.0 version Excel (Office 2003), and it might be wrong.");
            //    m_objExcel.Quit();
            //    return;
            //}

            m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
            if (TemplateFilePath.Equals(String.Empty))
            {
                m_objBook = (Excel._Workbook)(m_objBooks.Add(m_objOpt));
            }
            else
            {
                m_objBook = m_objBooks.Open(TemplateFilePath, m_objOpt, m_objOpt, m_objOpt, m_objOpt,
                    m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);                
            }
            m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
            m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1));
            m_objSheet.Name = "definition";
            m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(2));
            m_objSheet.Name = "image";
            m_objExcel.WorkbookBeforeClose += new Microsoft.Office.Interop.Excel.AppEvents_WorkbookBeforeCloseEventHandler(m_objExcel_WorkbookBeforeClose);
        }

        private void m_objExcel_WorkbookBeforeClose(Excel.Workbook m_objBooks, ref bool _Cancel)
        {
            //MessageBox.Show("Save finished.");
        }

        public void InsertPicture(string RangeName, string PicturePath)
        {
            m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(2));
            m_objSheet.Activate();
            m_objRange = m_objSheet.get_Range(RangeName, m_objOpt);
            m_objRange.Select();
            Excel.Pictures pics = (Excel.Pictures)m_objSheet.Pictures(m_objOpt);
            pics.Insert(PicturePath, m_objOpt);
        }

        public void InsertPicture(string RangeName, string PicturePath, float PictureWidth, float PictureHeight)
        {
            m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(2));
            m_objSheet.Activate();
            m_objRange = m_objSheet.get_Range(RangeName, m_objOpt);
            m_objRange.Select();

            float PicLeft, PicTop;
            PicLeft = Convert.ToSingle(m_objRange.Left);
            PicTop = Convert.ToSingle(m_objRange.Top);

            m_objSheet.Shapes.AddPicture(PicturePath, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoTrue,
                PicLeft, PicTop, PictureWidth, PictureHeight);
        }

        public void InsertDGV(DataGridView[] dgv, int num)
        {
            m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1));
            m_objSheet.Activate();
            int tmpr = 1;
            int tmpc = 1;
            m_objExcel.Cells[tmpr, tmpc] = DateTime.Now.ToString();
            tmpr = 4;
            tmpc = 2;
            for (int t = 0; t < num; t++)
            {
                int row = dgv[t].RowCount;
                int col = dgv[t].ColumnCount;
                for (int i = 0; i < row; i++)
                {
                    for (int j = 0; j < col; j++)
                    {
                        if (t == 0 && j > 0)
                        {
                            if (dgv[t][j, i].Value != null)
                            {
                                m_objExcel.Cells[tmpr + i, tmpc + j * 3] = " " + dgv[t][j, i].Value.ToString();
                            }
                            else
                            { }
                        }
                        else
                        {
                            if (dgv[t][j, i].Value != null)
                            {
                                m_objExcel.Cells[tmpr + i, tmpc + j] = " " + dgv[t][j, i].Value.ToString();
                            }
                            else
                            { }
                        }
                    }
                }
                // set border
                if (t == 1)
                {
                    m_objRange = m_objSheet.get_Range(m_objExcel.Cells[tmpr + 0, tmpc + 0], m_objExcel.Cells[tmpr + row - 1, tmpc + col - 1]);
                    m_objRange.Columns.AutoFit();
                    m_objRange.Borders.LineStyle = 1;
                    m_objRange.Font.Bold = true;
                }
                tmpr += (row + 3);

                // set auto fit 
                //Range 
            }
        }

        public void SaveFile(string OutputFilePath)
        {
            m_objBook.SaveAs(OutputFilePath, m_objOpt, m_objOpt,
                m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange,
                m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);
            this.Close();
        }

        private void Close()
        {
            m_objBook.Close(false, m_objOpt, m_objOpt);
            m_objExcel.Quit();
        }

        public void Dispose()
        {
            ReleaseObj(m_objSheets);
            ReleaseObj(m_objBook);
            ReleaseObj(m_objBooks);
            ReleaseObj(m_objExcel);
            System.GC.Collect();
            System.GC.WaitForPendingFinalizers();
        }

        private void ReleaseObj(object o)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(o);
            }
            catch { }
            finally { o = null; }
        }
    }
}
